USE [Mentoring2014]
GO

--------------------------------------------------------------
--Get all persons

declare @name nvarchar(50) = 'GetPersons'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[GetPersons]
as
begin
	
	select [ID], [NAME], [BIRTH_DATE] from [dbo].[Person]
	
end
go


--------------------------------------------------------------
--Get page persons
declare @name nvarchar(50) = 'GetPagePersons'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[GetPagePersons]
	@PageNumber int = 0,
	@PageSize int = 10
as
begin
	
	select [ID], [NAME], [BIRTH_DATE]
	from (
		select top (@PageNumber * @PageSize) [ID], [NAME], [BIRTH_DATE], row_number() over (order by ID) as ROW_NUM
		from [dbo].[Person]
	) t
	where t.[ROW_NUM] between ((@PageNumber - 1) * @PageSize) + 1 and @PageSize * (@PageNumber)
	
end
go

--------------------------------------------------------------
--Get rewards
declare @name nvarchar(50) = 'GetRewards'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[GetRewards]
as
begin
	
	select [ID], [TITLE], [DESCRIPTION], [IMAGE] from [dbo].[Reward]
	
end
go

--------------------------------------------------------------
--Get person rewards
declare @name nvarchar(50) = 'GetPersonRewards'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[GetPersonRewards]
	@PersonId uniqueidentifier
as
begin
	
	select r.[ID], r.[DESCRIPTION], r.[IMAGE], r.[TITLE]
	from [dbo].[PersonReward] pr inner join [dbo].[Reward] r on pr.[PERSON_ID] = r.[ID]
	where pr.[PERSON_ID] = @PersonId
	
end
go

--------------------------------------------------------------
--Add person
declare @name nvarchar(50) = 'AddPerson'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[AddPerson]
	@Name nvarchar(100),
	@BirthDate date
as
begin
	
	insert into [dbo].[Person] ([ID], [BIRTH_DATE], [NAME])
	values (newid(), @BirthDate, @Name)
	
end
go

--------------------------------------------------------------
--Add reward
declare @name nvarchar(50) = 'AddReward'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[AddReward]
	@Title nvarchar(100),
	@Description nvarchar(max),
	@Image uniqueidentifier = null
as
begin
	
	insert into [dbo].[Reward] ([ID], [DESCRIPTION], [IMAGE], [TITLE])
	values (newid(), @Description, @Image, @Title)
	
end
go

--------------------------------------------------------------
--Add person reward
declare @name nvarchar(50) = 'AddPersonReward'

if not exists (select 1 from [dbo].[sysobjects] where [id] = object_id(N'[dbo].'+ @name +'') and objectproperty([id], N'IsProcedure') = 1)
begin
	exec ('create procedure [dbo].' + @name + ' as raiserror(''Not implemented.'', 0,0)')
end
go


alter procedure [dbo].[AddPersonReward]
	@PersonId uniqueidentifier,
	@RewardId uniqueidentifier
as
begin
	
	insert into [dbo].[PersonReward] ([PERSON_ID], [REWARD_ID])
	values (@PersonId, @RewardId)
	
end
go